﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace KYJDAL
{
    public class SQLiteService
    {
        public void SetConnStr(string connString)
        {
            SQLiteHelper.connString = connString;
        }

        /// <summary>
        /// 判断数据表是否存在
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public bool IsTableExits(string tableName)
        {
            string sql = "select Count(*) from sqlite_master where type='table' and name=@table";
            SQLiteParameter[] param = new SQLiteParameter[]
            {
                new SQLiteParameter("@table",tableName)
            };
            try
            {
                object value = SQLiteHelper.ExecuteScalar(sql, param);
                return int.Parse(value.ToString()) == 1;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// 删除数据表
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public bool DeleteTable(string tableName)
        {
            string sql = "drop table " + tableName;
            try
            {
                SQLiteHelper.ExecuteNonQuery(sql);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// 清除表数据内存
        /// </summary>
        /// <returns></returns>
        public bool VacuumTable()
        {
            string sql = "Vacuum";
            try
            {
                SQLiteHelper.ExecuteNonQuery(sql);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// 创建数据表
        /// </summary>
        /// <param name="tableNaem"></param>
        /// <param name="columns"></param>
        /// <param name="isPrimaryKey"></param>
        /// <returns></returns>
        public bool CreateTable(string tableName, List<string> columns, bool isPrimaryKey = true)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("create table ");
            stringBuilder.Append(tableName + "(");
            if (isPrimaryKey)
            {
                stringBuilder.Append("ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,");
            }
            for (int i = 0; i < columns.Count; i++)
            {
                //如果是最后一个字段
                if (i == columns.Count - 1)
                {
                    stringBuilder.Append(columns[i] + " varchar(20)");
                }
                else
                {
                    stringBuilder.Append(columns[i] + " varchar(20), ");
                }
            }
            stringBuilder.Append(")");
            string str = stringBuilder.ToString();
            try
            {
                SQLiteHelper.ExecuteNonQuery(stringBuilder.ToString());
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
    }
}
